package com.zzz.excelanalyse

import android.util.ArrayMap
import android.util.Log
import android.util.Xml
import jxl.Workbook
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.WorkbookFactory
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.xmlpull.v1.XmlPullParser
import java.io.File
import java.util.zip.ZipEntry
import java.util.zip.ZipFile
import java.util.zip.ZipInputStream


fun String.sheetName(): String {
    val indexPre = this.lastIndexOf(File.separator)
    val indexEnd = this.lastIndexOf('.')
    return this.substring(indexPre + 1, indexEnd)
}

/**
 * 数据存储<sheetName ,存储从左到右，从上到下>
 */
class ExcelManager private constructor() {
    companion object {
        val INSTANCE = SingleHolder.INSTANCE
    }


    private object SingleHolder {
        val INSTANCE = ExcelManager()
    }

    /**
     * 使用jxl解析xls
     */
    fun xlsJxl(file: File): Map<String, List<List<String>>> {
        val result = ArrayMap<String, List<List<String>>>()
        Workbook.getWorkbook(file).sheets.forEach { sheet ->
            val rowsList = ArrayList<List<String>>()//列数据
            val sheetName = sheet.name
            for (i in 0 until sheet.rows) {
                rowsList.add(
                    sheet.getRow(i).map { it.contents }
                )
            }
            result[sheetName] = rowsList
        }
        return result
    }

    fun xlsxSelf(file: File): Map<String, List<List<String>>> {
        val result = ArrayMap<String, List<List<String>>>()
        //获取所有元素
        val zipFile = ZipFile(file)
        val cellsList = ArrayList<String>()
        val xmlPullParser = Xml.newPullParser()
        zipFile.getInputStream(zipFile.getEntry("xl/sharedStrings.xml"))
            .use { inputStream ->
                xmlPullParser.setInput(inputStream, "utf-8")
                var eventType = xmlPullParser.eventType
                while (eventType != XmlPullParser.END_DOCUMENT) {
                    if (eventType == XmlPullParser.START_TAG && xmlPullParser.name == "t") {
                        cellsList.add(xmlPullParser.nextText())
                    }
                    eventType = xmlPullParser.next()
                }
            }
        //获取cell对应的index,并添加到Map中保存
        file.inputStream().use { inputStream ->
            ZipInputStream(inputStream).use { zis ->
                var zipDir: ZipEntry? = null
                while ({ zipDir = zis.nextEntry;zipDir }() != null) {
                    if (zipDir!!.name.startsWith("xl/worksheets/", true) &&
                        zipDir!!.name.endsWith("xml", true)
                    ) {
                        val rowsList = ArrayList<List<String>>()
                        zipFile.getInputStream(zipFile.getEntry(zipDir!!.name))
                            .use { inputStream ->
                                xmlPullParser.setInput(inputStream, "utf-8")
                                val itemList: ArrayList<String> = ArrayList()//用于保存每行的数据
                                var eventType = xmlPullParser.eventType
                                while (eventType != XmlPullParser.END_DOCUMENT) {
                                    when (eventType) {
                                        XmlPullParser.START_TAG -> {
                                            if (xmlPullParser.name == "row") {
                                                itemList.clear()//每行数据开始清空容器
                                            } else if (xmlPullParser.name == "v") {
                                                val index = xmlPullParser.nextText()
                                                itemList.add(cellsList[index.toInt()])
                                            }
                                        }
                                        XmlPullParser.END_TAG -> {
                                            if (xmlPullParser.name == "row") {
                                                //将行数据保存
                                                rowsList.add(itemList.toList())
                                            }
                                        }
                                    }
                                    eventType = xmlPullParser.next()
                                }
                                //
                                result[zipDir!!.name.sheetName()] = rowsList
                            }
                    }
                }
            }
        }
        return result
    }

    /**
     * 使用POI解析xls、xlsx
     */
    fun excelPOI(file: File): Map<String, List<List<String>>> {
        val result = ArrayMap<String, List<List<String>>>()
        WorkbookFactory.create(file).use { workBook ->
            for (sheetIndex in 0 until workBook.numberOfSheets) {
                val rowsList = ArrayList<List<String>>()
                val sheet = workBook.getSheetAt(sheetIndex)
                sheet.rowIterator()
                    .forEach { row ->
                        val itemList: ArrayList<String> = ArrayList()//用于保存每行的数据
                        row.cellIterator().forEach { cell ->
                            itemList.add(cell.stringCellValue)
                        }
                        rowsList.add(itemList)
                    }
                result[sheet.sheetName] = rowsList
            }
        }
        return result
    }

}